{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "60f394d7-82f1-4326-9109-82478dfd9dd9",
   "metadata": {},
   "source": [
    "The Portfolio Analysis App demonstrates the powerful [Tabulator](../reference/widgets/Tabulator.ipynb) table that ships with Panel.\n",
    "\n",
    "<img style=\"max-height:500px\" src=\"https://assets.holoviz.org/panel/gifs/portfolio_analyzer.gif\"></img>\n",
    "\n",
    "This app is heavily inspired by the Dash AG Grid App [here](https://github.com/plotly/dash-ag-grid/blob/main/docs/demo_stock_portfolio.py). Having both enables you to compare [pros and cons of Panel w. Tabulator versus Dash w. AG Grid](https://github.com/holoviz/panel/issues/4341)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06ac7f7d-4966-446e-8b2b-ef47450e0642",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "import panel as pn\n",
    "\n",
    "pn.extension('plotly', 'tabulator')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e14ccb07-dcd5-4d09-ae67-1a80f82eb4d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "ACCENT = \"#BB2649\"\n",
    "RED = \"#D94467\"\n",
    "GREEN = \"#5AD534\"\n",
    "\n",
    "LINK_SVG = \"\"\"\n",
    "<svg xmlns=\"http://www.w3.org/2000/svg\" width=\"16\" height=\"16\" fill=\"currentColor\" class=\"bi bi-arrow-up-right-square\" viewBox=\"0 0 16 16\">\n",
    "  <path fill-rule=\"evenodd\" d=\"M15 2a1 1 0 0 0-1-1H2a1 1 0 0 0-1 1v12a1 1 0 0 0 1 1h12a1 1 0 0 0 1-1V2zM0 2a2 2 0 0 1 2-2h12a2 2 0 0 1 2 2v12a2 2 0 0 1-2 2H2a2 2 0 0 1-2-2V2zm5.854 8.803a.5.5 0 1 1-.708-.707L9.243 6H6.475a.5.5 0 1 1 0-1h3.975a.5.5 0 0 1 .5.5v3.975a.5.5 0 1 1-1 0V6.707l-4.096 4.096z\"/>\n",
    "</svg>\n",
    "\"\"\"\n",
    "\n",
    "CSV_URL = \"https://datasets.holoviz.org/equities/v1/equities.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c831a15-da71-4371-9f8a-a04dd464019f",
   "metadata": {},
   "source": [
    "Lets define our list of equities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47e4aac2-a07c-4d6c-91d9-eefa76efbd89",
   "metadata": {},
   "outputs": [],
   "source": [
    "EQUITIES = {\n",
    "    \"AAPL\": \"Apple\",\n",
    "    \"MSFT\": \"Microsoft\",\n",
    "    \"AMZN\": \"Amazon\",\n",
    "    \"GOOGL\": \"Alphabet\",\n",
    "    \"TSLA\": \"Tesla\",\n",
    "    \"BRK-B\": \"Berkshire Hathaway\",\n",
    "    \"UNH\": \"United Health Group\",\n",
    "    \"JNJ\": \"Johnson & Johnson\",\n",
    "}\n",
    "EQUITY_LIST = tuple(EQUITIES.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22487003-4910-4ae1-95e1-4e871ddd4535",
   "metadata": {},
   "source": [
    "## Extract the data\n",
    "\n",
    "We would be using *caching* (`pn.cache`) to improve the performance of the app if we where loading data from a live data source like `yfinance`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "babaf3df-678d-4aea-8dd6-300201f13bf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "@pn.cache(ttl=600)\n",
    "def get_historical_data(tickers=EQUITY_LIST, period=\"2y\"):\n",
    "    \"\"\"Downloads the historical data from Yahoo Finance\"\"\"\n",
    "    df = pd.read_csv(CSV_URL, index_col=[0, 1], parse_dates=['Date'])\n",
    "    return df\n",
    "\n",
    "historical_data = get_historical_data()\n",
    "historical_data.head(3).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a50540fa-5f12-4b84-95b0-13a935f8319c",
   "metadata": {},
   "source": [
    "## Transform the data\n",
    "\n",
    "Let us calculate the `summary_data` to show in the Table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3c8cf02-327d-4cbb-abfe-8f9f9d9eac63",
   "metadata": {},
   "outputs": [],
   "source": [
    "def last_close(ticker, data=historical_data):\n",
    "    \"\"\"Returns the last close pricefor the given ticker\"\"\"\n",
    "    return data.loc[ticker][\"Close\"].iloc[-1]\n",
    "\n",
    "last_close(\"AAPL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3f6da46-3f83-48e6-9060-27751300861d",
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_data_dict = {\n",
    "    \"ticker\": EQUITY_LIST,\n",
    "    \"company\": EQUITIES.values(),\n",
    "    \"info\": [\n",
    "        f\"\"\"<a href='https://finance.yahoo.com/quote/{ticker}' target='_blank'>\n",
    "        <div title='Open in Yahoo'>{LINK_SVG}</div></a>\"\"\"\n",
    "        for ticker in EQUITIES\n",
    "    ],\n",
    "    \"quantity\": [75, 40, 100, 50, 40, 60, 20, 40],\n",
    "    \"price\": [last_close(ticker) for ticker in EQUITIES],\n",
    "    \"value\": None,\n",
    "    \"action\": [\"buy\", \"sell\", \"hold\", \"hold\", \"hold\", \"hold\", \"hold\", \"hold\"],\n",
    "    \"notes\": [\"\" for i in range(8)],\n",
    "}\n",
    "\n",
    "summary_data = pd.DataFrame(summary_data_dict)\n",
    "\n",
    "def get_value_series(data=summary_data):\n",
    "    \"\"\"Returns the quantity * price series\"\"\"\n",
    "    return data[\"quantity\"] * data[\"price\"]\n",
    "\n",
    "summary_data[\"value\"] = get_value_series()\n",
    "summary_data.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ddedd48-a25a-4e79-8f7b-720925c47df0",
   "metadata": {},
   "source": [
    "## Define the *Summary Table*\n",
    "\n",
    "We define the configuration of the Tabulator below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5000956-5189-42fc-b685-00d461db0e65",
   "metadata": {},
   "outputs": [],
   "source": [
    "titles = {\n",
    "    \"ticker\": \"Stock Ticker\",\n",
    "    \"company\": \"Company\",\n",
    "    \"info\": \"Info\",\n",
    "    \"quantity\": \"Shares\",\n",
    "    \"price\": \"Last Close Price\",\n",
    "    \"value\": \"Market Value\",\n",
    "    \"action\": \"Action\",\n",
    "    \"notes\": \"Notes\",\n",
    "}\n",
    "frozen_columns = [\"ticker\", \"company\"]\n",
    "editors = {\n",
    "    \"ticker\": None,\n",
    "    \"company\": None,\n",
    "    \"quantity\": {\"type\": \"number\", \"min\": 0, \"step\": 1},\n",
    "    \"price\": None,\n",
    "    \"value\": None,\n",
    "    \"action\": {\n",
    "        \"type\": \"list\",\n",
    "        \"values\": {\"buy\": \"buy\", \"sell\": \"sell\", \"hold\": \"hold\"},\n",
    "    },\n",
    "    \"notes\": {\n",
    "        \"type\": \"textarea\",\n",
    "        \"elementAttributes\": {\"maxlength\": \"100\"},\n",
    "        \"selectContents\": True,\n",
    "        \"verticalNavigation\": \"editor\",\n",
    "        \"shiftEnterSubmit\": True,\n",
    "    },\n",
    "    \"info\": None,\n",
    "}\n",
    "\n",
    "widths = {\"notes\": 400}\n",
    "formatters = {\n",
    "    \"price\": {\"type\": \"money\", \"decimal\": \".\", \"thousand\": \",\", \"precision\": 2},\n",
    "    \"value\": {\"type\": \"money\", \"decimal\": \".\", \"thousand\": \",\", \"precision\": 0},\n",
    "    \"info\": {\"type\": \"html\", \"field\": \"html\"},\n",
    "}\n",
    "\n",
    "text_align = {\n",
    "    \"price\": \"right\",\n",
    "    \"value\": \"right\",\n",
    "    \"action\": \"center\",\n",
    "    \"info\": \"center\",\n",
    "}\n",
    "base_configuration = {\n",
    "    \"clipboard\": \"copy\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8ba0bd7-a8ae-4a71-888a-94f70538a468",
   "metadata": {},
   "source": [
    "Here we define the `summary_table` *widget*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b4ed809-9844-4628-83da-466afc1f60ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_table = pn.widgets.Tabulator(\n",
    "    summary_data,\n",
    "    editors=editors,\n",
    "    formatters=formatters,\n",
    "    frozen_columns=frozen_columns,\n",
    "    layout=\"fit_data_table\",\n",
    "    selectable=1,\n",
    "    show_index=False,\n",
    "    text_align=text_align,\n",
    "    titles=titles,\n",
    "    widths=widths,\n",
    "    configuration=base_configuration,\n",
    ")\n",
    "summary_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb9cc6ff-04b4-485e-848f-cabd2576c0cb",
   "metadata": {},
   "source": [
    "Now lets *style* the table using the *Pandas styler* api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2a10653-125d-4735-a202-202a8e384b7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def style_of_action_cell(value, colors={'buy': GREEN, 'sell': RED}):\n",
    "    \"\"\"Returns the css to apply to an 'action' cell depending on the val\"\"\"\n",
    "    return f'color: {colors[value]}' if value in colors else ''\n",
    "\n",
    "summary_table.style.map(style_of_action_cell, subset=[\"action\"]).set_properties(\n",
    "    **{\"background-color\": \"#444\"}, subset=[\"quantity\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cb5379a-6664-4b84-bf37-7c04d7a555ab",
   "metadata": {},
   "source": [
    "For later we also need a function to handle when a user edits a cell in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91b9e20b-2233-41ff-934d-f1702a4b3052",
   "metadata": {},
   "outputs": [],
   "source": [
    "patches = pn.widgets.IntInput(description=\"Used to raise an event when a cell value has changed\")\n",
    "\n",
    "def handle_cell_edit(event, table=summary_table):\n",
    "    \"\"\"Updates the `value` cell when the `quantity` cell is updated\"\"\"\n",
    "    row = event.row\n",
    "    column = event.column\n",
    "    if column == \"quantity\":\n",
    "        quantity = event.value\n",
    "        price = summary_table.value.loc[row, \"price\"]\n",
    "        value = quantity * price\n",
    "        table.patch({\"value\": [(row, value)]})\n",
    "\n",
    "        patches.value +=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f3e8f0b-70ef-41ee-9259-2a8f82940c76",
   "metadata": {},
   "source": [
    "## Define the plots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efe47a55-3e77-4b5f-9b43-4d8ea4e8b4ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "def candlestick(selection=[], data=summary_data):\n",
    "    \"\"\"Returns a candlestick plot\"\"\"\n",
    "    if not selection:\n",
    "        ticker = \"AAPL\"\n",
    "        company = \"Apple\"\n",
    "    else:\n",
    "        index = selection[0]\n",
    "        ticker = data.loc[index, \"ticker\"]\n",
    "        company = data.loc[index, \"company\"]\n",
    "\n",
    "    dff_ticker_hist = historical_data.loc[ticker].reset_index()\n",
    "    dff_ticker_hist[\"Date\"] = pd.to_datetime(dff_ticker_hist[\"Date\"])\n",
    "\n",
    "    fig = go.Figure(\n",
    "        go.Candlestick(\n",
    "            x=dff_ticker_hist[\"Date\"],\n",
    "            open=dff_ticker_hist[\"Open\"],\n",
    "            high=dff_ticker_hist[\"High\"],\n",
    "            low=dff_ticker_hist[\"Low\"],\n",
    "            close=dff_ticker_hist[\"Close\"],\n",
    "        )\n",
    "    )\n",
    "    fig.update_layout(\n",
    "        title_text=f\"{ticker} {company} Daily Price\",\n",
    "        template=\"plotly_dark\",\n",
    "        autosize=True,\n",
    "    )\n",
    "    return fig\n",
    "\n",
    "pn.pane.Plotly(candlestick())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b0fd432-f167-43c7-a5ff-b23ccf427080",
   "metadata": {},
   "outputs": [],
   "source": [
    "def portfolio_distribution(patches=0):\n",
    "    \"\"\"Returns the distribution of the portfolio\"\"\"\n",
    "    data = summary_table.value\n",
    "    portfolio_total = data[\"value\"].sum()\n",
    "\n",
    "    fig = px.pie(\n",
    "        data,\n",
    "        values=\"value\",\n",
    "        names=\"ticker\",\n",
    "        hole=0.3,\n",
    "        title=f\"Portfolio Total $ {portfolio_total:,.0f}\",\n",
    "        template=\"plotly_dark\",\n",
    "    )\n",
    "    fig.layout.autosize = True\n",
    "    return fig\n",
    "\n",
    "pn.pane.Plotly(portfolio_distribution())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e25cad3-c418-4423-8d23-2fc460b4ca10",
   "metadata": {},
   "source": [
    "## Bind the widgets and functions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19948be6-f4df-4d88-aa58-8f9038e1cf15",
   "metadata": {},
   "source": [
    "We want the `candlestick` plot to depend on the selection in `summary_table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc0f99f9-4cc4-4b89-bd8b-ad143eadc6c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "candlestick = pn.bind(candlestick, selection=summary_table.param.selection)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53747c38-38a2-4f7e-891e-02bf6267e482",
   "metadata": {},
   "source": [
    "We want the `portfolio_distribution` to be updated when ever a cell value changes in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4405258-1671-4ef4-b830-821703b66c30",
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_table.on_edit(handle_cell_edit)\n",
    "\n",
    "portfolio_distribution = pn.bind(portfolio_distribution, patches=patches)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5024d93f-f17f-4dc8-b251-493bd9f772d7",
   "metadata": {},
   "source": [
    "## Test the app"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b3f2e30-5a5a-4fc3-a925-b357f236415b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.Column(\n",
    "    pn.Row(\n",
    "        pn.pane.Plotly(candlestick), \n",
    "        pn.pane.Plotly(portfolio_distribution)\n",
    "    ),\n",
    "    summary_table,\n",
    "    height=600\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad8b4f4d-81ff-4f1e-a57a-8f666516d5f5",
   "metadata": {},
   "source": [
    "## Layout the app in a nice template\n",
    "\n",
    "We will use the `FastGridTemplate` which provides a nice dashboard layout with Panels you can resize and move around interactively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "128fb9f8-b209-4168-a150-1199cbe4e8f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "template = pn.template.FastGridTemplate(\n",
    "    title=\"Portfolio Analysis\",\n",
    "    accent_base_color=ACCENT,\n",
    "    header_background=ACCENT,\n",
    "    prevent_collision=True,\n",
    "    save_layout=True,\n",
    "    theme_toggle=False,\n",
    "    theme='dark',\n",
    "    row_height=160\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd56569a-14f6-425c-a049-ad7a6b1bd7d1",
   "metadata": {},
   "source": [
    "Lets add the plots and table to the template"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0de76e59-15f6-4ad7-9379-7b0fde1e0602",
   "metadata": {},
   "outputs": [],
   "source": [
    "template.main[0:3, 0:8]  = pn.pane.Plotly(candlestick)\n",
    "template.main[0:3, 8:12] = pn.pane.Plotly(portfolio_distribution)\n",
    "template.main[3:5, :]    = summary_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7169d20-4fdc-4040-82c4-1171985c711d",
   "metadata": {},
   "source": [
    "The template does not display in a notebook so we only output it when in a *server* context."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5a7878f-e4a6-43a6-a1f4-640ca4c6403c",
   "metadata": {},
   "outputs": [],
   "source": [
    "if pn.state.served:\n",
    "    template.servable()"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
